package DB;

import Model.Attendance;
import Model.Session;
import Model.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class AttendanceDB implements AttendanceDBIF {

    private Connection conn;
    private StudentDB studentDB;
    private SessionDB sessionDB;

    public AttendanceDB() {
        conn = ConnectionDB.getInstance().getConn();
        studentDB = new StudentDB();
        sessionDB = new SessionDB();
    }

    @Override
    public int insertAttendance(Attendance attendance) {
        int rc = -1;
        PreparedStatement pstmt;

        String query = "INSERT INTO Attendance (attendanceId, comment, isAttending, studentId, sessionId) VALUES (?,?,?,?,?)";
        System.out.println("Inserting: " + query);

        try {
            pstmt = conn.prepareStatement(query);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1, attendance.getId());
            pstmt.setString(2, attendance.getComment());
            pstmt.setBoolean(3, attendance.getIsAttending());
            pstmt.setInt(4, attendance.getStudentId().getStudentId());
            pstmt.setInt(5, attendance.getSessionId().getId());
            rc = pstmt.executeUpdate();
            pstmt.close();

            System.out.println(attendance.getId() + " " + attendance.getComment() + " " + attendance.getIsAttending() + " " + attendance.getStudentId().getStudentId() + " " + attendance.getSessionId().getId());
        }
        catch (Exception e) {
            System.out.println("Inserting attendance exception: " + e);
        }

        return rc;
    }

    @Override
    public Attendance findAttendance(int id) {
        return singleWhere("attendanceId = " + id, false);
    }

    @Override
    public ArrayList<Attendance> findAttendanceByStudentID(int id) {
        return miscWhere("studentId = " + id, false);
    }

    @Override
    public Attendance getAttendanceByStudentAndSessionId(Student student, Session session) {
        return singleWhere("studentId = " + student.getId() + " AND sessionId = "  + session.getId(), false);
    }

    @Override
    public ArrayList<Attendance> getAllAttendancesBySessionID(Session session) {
        return miscWhere("sessionId = " + session.getId(), false);
    }

    @Override
    public ArrayList<Attendance> getAllAttendances(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public int updateAttendance(Attendance attendance) {
        Attendance attendanceObj = attendance;
        int rc = -1;

        String query = "UPDATE Attendance SET " +
                "comment = '" + attendanceObj.getComment() + "', " +
                "isAttending = '" + attendanceObj.getIsAttending() + "', " +
                "studentId = '" + attendanceObj.getStudentId().getStudentId() + "', " +
                "sessionId = '" + attendanceObj.getSessionId().getId() + "'" +
                " WHERE attendanceId = '" + attendanceObj.getId() + "'";

        System.out.println("Update query: " + query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Update exception in AttendanceDB: " +e);
        }
        return rc;
    }

    @Override
    public int deleteAttendance(int id) {
        return 0;
    }

    //Finding a single Attendance
    private Attendance singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Attendance attendanceObj = new Attendance();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                attendanceObj = buildAttendance(results);
                stmt.close();
            }
            else {
                attendanceObj = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }

        return attendanceObj;
    }

    //Finding all Attendances
    private ArrayList<Attendance> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Attendance> list = new ArrayList<Attendance>();
        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Attendance attendanceObj = buildAttendance(results);
                list.add(attendanceObj);
            }

            stmt.close();

        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT attendanceId, comment, isAttending, studentId, sessionId FROM Attendance";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Method to build a Attendance object
    private Attendance buildAttendance(ResultSet results) {

        Attendance attendanceObj = new Attendance();

        try {
            attendanceObj.setId(results.getInt("attendanceId"));
            attendanceObj.setComment(results.getString("comment"));
            attendanceObj.setIsAttending(results.getBoolean("isAttending"));
            attendanceObj.setStudentId(studentDB.findStudentByID(results.getInt("studentId")));
            attendanceObj.setSessionId(sessionDB.findSession(results.getInt("sessionId")));
        }
        catch (Exception e) {
            System.out.println("Error building Attendance object");
        }

        return attendanceObj;
    }

    public int attendanceGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(attendanceId) from Attendance");
        nextId = nextId +1;
        return nextId;
    }
}
